package org.finalministry.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * See <a
 * href="https://code.google.com/p/genormous/wiki/DBUpdateStrategy">Update
 * strategy for databases</a> on google.com for more information.
 * 
 * @author admin
 * 
 */
public class DBUpdate {

	private Connection connection;

	/**
	 * 1 - Created initial schema 2 - Added driver column to race_car table
	 */
	public static final int DB_VERSION = 1;

	/**
	 * When setting the main version the sub version always goes to zero
	 */
	private static final String SET_VERSION_SQL = "UPDATE database_version SET main_version = ?";
	private static final String HAS_UPDATE_SQL = "SELECT update_id FROM updates WHERE update_id = ?";
	private static final String ADD_UPDATE_SQL = "INSERT INTO updates values (?)";

	private static final String GET_VERSION_SQL = "SELECT VERSION FROM DATABASE_VERSION";

	// Add a bunch of utility methods for adding columns, removing columns, etc.

	/**
	 * Returns true if the update is added to the database
	 */
	private static boolean addUpdate(Connection con, String updateId) throws SQLException {
		boolean hasUpdate;

		PreparedStatement ps = con.prepareStatement(HAS_UPDATE_SQL);
		ps.setString(1, updateId);
		ResultSet rs = ps.executeQuery();
		hasUpdate = rs.next();
		rs.close();
		ps.close();

		if (!hasUpdate) {
			System.out.println("Adding update " + updateId);
			ps = con.prepareStatement(ADD_UPDATE_SQL);
			ps.setString(1, updateId);
			ps.executeUpdate();
			ps.close();

			return (true);
		} else {
			System.out.println("Skipping update " + updateId);
			return (false);
		}
	}

	/**
	 * This is where all the magic happens. Somewhere in your startup code you
	 * will call this method right after initializing a database connection
	 * 
	 * @throws SQLException
	 */
	public void checkUpdate() throws SQLException {
		Connection con = getConnection();
		con.setAutoCommit(false); // This is important as you want to be in a
									// transaction
		int version = 0;

		// Get the database version
		Statement stmt = con.createStatement();
		stmt.execute(GET_VERSION_SQL);
		ResultSet rs = stmt.getResultSet();
		if (rs.next())
			version = rs.getInt(1);
		rs.close();
		stmt.close();
		// I like to print out my version durring startup.
		System.out.println("Database version is " + version);

		// This prevents running old code on a newer database
		if (version > DB_VERSION)
			throw new SQLException("Database is newer then code");

		try {
			if (version < DB_VERSION) {
				switch (version) {
				case 0:
					/*
					 * I like to add this printUpdate method it serves two
					 * purposes. 1. It informs the user of what update is being
					 * ran (maybe to a log) 2. It self documents this code as to
					 * what update is really being ran
					 */
					printUpdate(1);
					if (addUpdate(con, "03d6c6bf-129d-41a1-a7e0-fe44f4361236"))
						createInitialSchema(con); // method that contains the
													// update

				case 1:
					printUpdate(2);
					if (addUpdate(con, "731c84d7-3f13-44a8-b1fe-4866f0079e8a"))
						version2Update(con); // method that contains the update

				case 2:
					printUpdate(3);
					if (addUpdate(con, "46568b39-4174-4d25-bec9-6eb0db1f18ae"))
						version3Update(con);
				}

				// This sets the database version to be current.
				PreparedStatement ps = con.prepareStatement(SET_VERSION_SQL);
				ps.setInt(1, DB_VERSION);
				ps.executeUpdate();
			}

			// Branch updates go here.

			// End branch updates

			con.commit();
		} catch (SQLException e) {
			// Log what happened
			// Roll back in case of a problem.
			con.rollback();
		} finally {
			con.close();
		}
	}

	private Connection getConnection() {

		return connection;
	}

	private void version3Update(Connection con) {
		// TODO Auto-generated method stub

	}

	private void version2Update(Connection con) {
		// TODO Auto-generated method stub

	}

	private void createInitialSchema(Connection con) {
		// TODO Auto-generated method stub

	}

	private void printUpdate(int i) {
		// TODO Auto-generated method stub

	}

}
